<?php
class Mailings extends Table
{

	public static function _Tab_($db, $sql)
	{
        $result = $db->query($sql);
        $nb = $result->num_rows;
        if (!$nb)
            echo LNG_MAI_VIDE;
        else
        {
?>
<table border='1'> 
    <thead>
        <tr>
            <th><?=LNG_MAI_DATE?></th>
            <th><?=LNG_MAI_TITRE?></th>
        </tr>
    </thead>
    <tbody>
<?php
            for ($i = 0; $i < $nb; $i++)
            {
                $row = $result->fetch_assoc();
?>
    <tr>
        <td><?=$row['mai_date']?></td>
        <td><? dohtml_lien('mailing.php?id='.$row['mai_id'], $row['mai_titre']) ?></td>
    </tr>
<?php
            }   
?>
    </tbody>
</table>
<?php
        $result->free();
        }    
    }//------------------------------------------------------------------Tableau

	public static function ConditionEmail(){//--------------------------
		return "(con_email IS NULL OR con_email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')";
    }//-----------------------------------------------------------------
	
	public static function ConditionAdresse(){//------------------------
		return "(cli_adresse = '' OR cli_codepostal = '' OR cli_ville = '')";
    }//-----------------------------------------------------------------

    public static function Tableau_mailing($db, $mai_id, $selection=-1, $adresse=-1)//-------------------------------------------------
    {
        $sql='SELECT env_mai_id as mai_id, env_envoi, cli_id, cli_nom, con_id, con_nom, con_prenom, con_fonction, con_annee'.
            ' FROM contacts'.
            ' JOIN clients ON clients.cli_id = contacts.con_cli_id'.
            ' JOIN envois ON envois.env_con_id = contacts.con_id';
        $where = array();
		if ($adresse==0)//mail
			$where[]= Mailings::ConditionEmail();
		if ($adresse==1)//postal
			$where[]= Mailings::ConditionAdresse();
		if ($selection!=-1)//contacts décochés ou cochés
			$where[]= "env_envoi=$selection";
		$where[]= " env_mai_id=$mai_id ";
		if (count($where)>0)			
			$sql .= " WHERE ".implode(' AND ', $where);
        $champs = array ('mai_liste', 'cli_nom', 'con_nom', 'con_fonction', 'con_annee');
        $champs_tri = array ('', 'cli_nom', 'con_nom', 'con_fonction', 'con_annee');
        $msg_vide = LNG_CON_VIDE;
		return parent::Tableau($db, $sql, $champs, $champs_tri, $msg_vide, true);
    }//------------------------------------------------------------------Tableau

	public static function Resume_Mailing($db, $mai_id){
		$envoi=" AND env_envoi=1";
		$sql = "SELECT count(distinct(con_cli_id)) AS nb 
				FROM envois 
				JOIN contacts ON contacts.con_id=envois.env_con_id 
				WHERE env_mai_id=$mai_id";
		if ($result=$db->query($sql.$envoi))
			if ($row=$result->fetch_assoc())
				$clients=$row['nb'];
		$sql = "SELECT count(env_con_id) AS nb 
				FROM envois 
				WHERE env_mai_id=$mai_id";
		if ($result=$db->query($sql.$envoi))
			if ($row=$result->fetch_assoc())
				$contacts=$row['nb'];
		$sql = "SELECT count(con_cli_id) AS nb 
				FROM envois 
				JOIN contacts ON contacts.con_id=envois.env_con_id 
				WHERE env_mai_id=$mai_id AND ".Mailings::ConditionEmail();
		if ($result=$db->query($sql.$envoi))
			if ($row=$result->fetch_assoc())
				$emails=$row['nb'];
		$sql = "SELECT count(env_con_id) AS nb 
				FROM envois 
				JOIN contacts ON contacts.con_id=envois.env_con_id 
				JOIN clients ON contacts.con_cli_id=clients.cli_id 
				WHERE env_mai_id=$mai_id AND ".Mailings::ConditionAdresse();
		if ($result=$db->query($sql.$envoi))
			if ($row=$result->fetch_assoc())
				$adresses=$row['nb'];
		echo "$contacts contacts selectionnés ($clients Clients), dont $emails sans e-mail et $adresses sans adresse<br/>";
		return Array(($emails==0),($adresses==0));	
	}

    
    // Ecrit le tableau des mailings
    public static function TableauCli($db, $cli_id)//-------------------------------------------------
    {
        $sql="SELECT con_cli_id, mai_id, mai_titre, mai_statut, DATE_FORMAT(mai_datedebut, '%d/%m/%Y') as mai_date".
            ' FROM mailings'.
            ' JOIN envois ON envois.env_mai_id = mailings.mai_id'.
            ' JOIN contacts ON contacts.con_id = envois.env_con_id'.
            " WHERE con_cli_id =$cli_id".
            ' ORDER BY mai_datedebut DESC';
        Mailings::_Tab_($db, $sql);
    }//-----------------------------------------------------------------Tableau
	
    // Ecrit le tableau des mailings
    public static function Tableau($db)//-------------------------------------------------
    {
        $sql="SELECT mai_id, mai_titre, mai_statut, DATE_FORMAT(mai_datedebut, '%d/%m/%Y') as mai_date".
            ' FROM mailings'.
            ' ORDER BY mai_datedebut DESC';
        Mailings::_Tab_($db, $sql);
    }//------------------------------------------------------------------Tableau
}//Class Mailings-------------------------------------------------------


class Mailing extends Enregistrement{
	// Constructeur
	function __construct($db){
		$this->defaut = Array('mai_id' 		=> -1,
						'mai_texte' 		=> '',
						'mai_titre' 		=> '',
						'mai_objet'			=> '',
						'mai_dateenvoi'		=> '',
						'mai_datedebut'		=> date('Y-m-d H:i:s'),
						'mai_type'			=> '',
						'mai_statut'		=> 1,
						'mai_commentaire'	=> '',
						'mai_fichier'		=> '',
						'mai_attachment'	=> Array(),
						'mai_crit_bra'		=> Array(),
						'mai_crit_uti'		=> Array(),
						'mai_crit_zon'		=> Array());					
		parent::__construct($db, 'mailings', 'mai_id');
        $this->externes = Array('mai_crit_bra', 'mai_crit_uti', 'mai_crit_zon', 'mai_attachment');
	}//__construct


	public function Ecrire(){
		$retour = parent::Ecrire();
		if ($retour){
			switch ($this->onglet){
				case 1:
					$sql = "DELETE FROM criteres WHERE cri_mai_id='{$this->Cle()}'";
					$this->db->query($sql);
					$sql = "INSERT INTO criteres (cri_mai_id, cri_table, cri_tab_id) VALUES ";
					$sql_cri = array();
					foreach	($this->attribut['mai_crit_bra'] as $cri){
						$sql_cri[] = "('".$this->Cle()."', 'b', '".$cri."')";
					}
					foreach	($this->attribut['mai_crit_uti'] as $cri){
						$sql_cri[] = "('".$this->Cle()."', 'u', '".$cri."')";
					}
					foreach	($this->attribut['mai_crit_zon'] as $cri){
						$sql_cri[] = "('".$this->Cle()."', 'z', '".$cri."')";
					}
					if (count($sql_cri)>0){
						$sql .= implode(', ', $sql_cri);
					}
					$this->db->query($sql);
					$sql = "DELETE FROM envois WHERE env_mai_id='{$this->Cle()}'";
					$this->db->query($sql);
					$sql="INSERT INTO envois (env_mai_id, env_con_id, env_envoi) (SELECT {$this->Cle()}, con_id, 1".
						' FROM contacts'.
						' JOIN clients ON clients.cli_id = contacts.con_cli_id'.
						' JOIN utilisateurs ON clients.cli_uti_id = utilisateurs.uti_id';
					$where = array();
					if (count($this->attribut['mai_crit_uti'])>0)
						$where[]= " cli_uti_id IN (".implode(',',$this->attribut['mai_crit_uti']).")";
					if (count($this->attribut['mai_crit_bra'])>0)
						$where[]= " cli_bra_id IN (".implode(',',$this->attribut['mai_crit_bra']).")";
					if (count($this->attribut['mai_crit_zon'])>0)
						$where[]= " cli_zon_id IN (".implode(',',$this->attribut['mai_crit_zon']).")";
					$sql .= " WHERE ".implode(' AND ', $where).")";					
					$this->db->query($sql);
					break;
				case 3:
					$sql = "DELETE FROM attachments WHERE att_mai_id='{$this->Cle()}'";
					$this->db->query($sql);
					$sql = "INSERT INTO attachments (att_mai_id, att_nom) VALUES ";
					$sql_cri = array();
					foreach($this->attribut['mai_attachment'] as $att){
						$sql_cri[] = "( '".$this->Cle()."', '".$att."')";
					}
					if (count($sql_cri)>0){
						$sql .= implode(', ', $sql_cri);
						$this->db->query($sql);
					}
					break;
			}
		}	
		return $retour;
	}

	
	public function Lire($id){
		$ok = parent::Lire($id);
		if ($ok){
			switch ($this->onglet){
				case 1:
				case 2:
					$this->attribut['mai_crit_bra'] = Array();
					$this->attribut['mai_crit_uti'] = Array();
					$this->attribut['mai_crit_zon'] = Array();
					$sql = "SELECT * FROM criteres WHERE cri_mai_id={$this->Cle()}";
					if ($result = $this->db->query($sql))
					{
						while ($row= $result->fetch_assoc()){
							switch ($row['cri_table']){
								case 'u':
									$this->attribut['mai_crit_uti'][] = $row['cri_tab_id'];
									break;
								case 'b':
									$this->attribut['mai_crit_bra'][] = $row['cri_tab_id'];
									break;
								case 'z':
									$this->attribut['mai_crit_zon'][] = $row['cri_tab_id'];
									break;
							}
						} 
					}					
					break;
				default:
					break;
			}
			$this->attribut['mai_attachment'] = Array();
			$sql = "SELECT att_nom FROM attachments WHERE att_mai_id={$this->Cle()}";
			if ($result = $this->db->query($sql)) {
				while ($row= $result->fetch_assoc()){
					$this->attribut['mai_attachment'][] = $row['att_nom'];
				} 
			}	
		}
		return $ok;
	}

	function NomFormat()
	{
		return parent::NomFormat('mai_titre');
	}


	function Traitement(&$erreur){
		if (isset($_POST['maj_criteres'])){
			if (isset($_POST['branches']))
				foreach ($_POST['branches'] as $item)
					$this->attribut['mai_crit_bra'][] = $item;
			if (isset($_POST['zones']))
				foreach ($_POST['zones'] as $item)
					$this->attribut['mai_crit_zon'][] = $item;
			if (isset($_POST['utilisateurs']))
				foreach ($_POST['utilisateurs'] as $item)
					$this->attribut['mai_crit_uti'][] = $item;
			$this->Ecrire();			
		}
		// Mise a jour du texte
		if (isset($_POST['maj_infos'])){
			// Si un document integré était prévu
			if ($this->attribut['mai_fichier']!=''){
				name2suppr($this->attribut['mai_fichier'], 'upload/');
				// Suppression de la référence dans l'objet
				$this->attribut['mai_fichier']='';
				$this->Ecrire();
			}
		}
		if (isset($_POST['send_content'])){
			$file_error = wrong_uploaded_file( $_FILES['avatar']['tmp_name'], 
												10000000, 
												Array('image', 'pdf') );
			switch ($file_error) {
				case ERR_INPUT_FILE_SIZE:
					$this->erreurs['mai_fichier'] = 'Le fichier est trop gros...';
					break;
				case ERR_INPUT_FILE_TYPE:
					$this->erreurs['mai_fichier'] = 'Ce fichier n\'est pas pris en charge';
					break;
				case ERR_INPUT_FILE_OK:
					if ( $fichier = doc2jpgs($_FILES['avatar']['tmp_name'], 'upload/', $_FILES['avatar']['name']) ){
						if ( $this->attribut['mai_fichier']!='' ){	
							name2suppr($this->attribut['mai_fichier'], 'upload/');
						}			
						$this->attribut['mai_fichier']=$fichier;
						$this->Ecrire();	
					}
					else
						$this->erreurs['mai_fichier'] = 'Echec de l\'upload !';
					break;
				default:			
			}
		}
		if (isset($_POST['send_attachment'])){
			$file_error = wrong_uploaded_file( $_FILES['avatar']['tmp_name'], 
												10000000);
			switch ($file_error) {
				case ERR_INPUT_FILE_SIZE:
					$this->erreurs['mai_fichier'] = 'Le fichier est trop gros...';
					break;
				case ERR_INPUT_FILE_TYPE:
					$this->erreurs['mai_fichier'] = 'Ce fichier n\'est pas pris en charge';
					break;
				case ERR_INPUT_FILE_OK:
					if ( move_uploaded_file($_FILES['avatar']['tmp_name'], 'upload/attachment/'.$_FILES['avatar']['name']) ){
						$this->attribut['mai_attachment'][] = $_FILES['avatar']['name'];
						$this->Ecrire();
					}
/*
					if ( $fichier = doc2jpgs($_FILES['avatar']['tmp_name'], 'upload/', $_FILES['avatar']['name']) ){
						if ( $this->attribut['mai_fichier']!='' ){	
							name2suppr($this->attribut['mai_fichier'], 'upload/');
						}			
						$this->attribut['mai_fichier']=$fichier;
						$this->Ecrire();	
					}
					else
						$this->erreurs['mai_fichier'] = 'Echec de l\'upload !';
*/
					break;
				default:
			}			
		}
		return parent::Traitement();	
	}


	function Contenu($erreur,$onglet=0, $edition=False)//-----------------------
	{
		$edition = !$this->attribut['mai_statut']<4 && $_SESSION[SES_USER_ID]>=GR_MAI_MODIF;
		switch ($onglet)
		{
			case 0:// général
?>
<form method='post' action='mailing.php?<?="id={$this->cle()}&edition=$edition"?>'>
<?php
				if (get_magic_quotes_gpc()){
				   $this->attribut['mai_commentaire'] = stripslashes( $this->attribut['mai_commentaire'] );
				}
				$this->ChampText('mai_titre', $edition);
				$this->ChampText('mai_objet', $edition);
				$this->ChampArea('mai_commentaire', $edition);
				if ($edition)
					dohtml_formbouton('labelagauche', 'Mettre à jour', 'maj_infos', 'Annuler');
?>
</form>
<?php				break;
			
			case 1:// critères
?>
<form method='post' action='mailingcritere.php?<?="id={$this->cle()}&edition=$edition"?>'>
<?php
if ($this->attribut['mai_statut']>3){
	echo "la liste des contacts a déja été modifiée";
}
// function CritereMailing
function CritereMailing($titre, $name, $choix, $select, $edition){
if ($edition)
	$liens=" (<a href=\"javascript:selectAll('$name',true)\">Tout cocher</a>/<a href=\"javascript:selectAll('$name', false)\">Tout décocher</a>)";
?>
<h4><?=($titre.$liens)?></h4>
<div class='criteremailing'>
<?php
if ($edition)
	foreach	($choix as $item){
		$check = (in_array($item[0], $select))? " checked='checked'": "";
		echo "<input type='checkbox' name='{$name}[]' value='{$item[0]}'$check>{$item[1]}<br/>";
	}
else
	foreach	($choix as $item)
		if (in_array($item[0], $select))
			echo "{$item[1]}<br/>";
?>
</div>
<?php	
}//function CritereMailing
?>
<div>
<?php 
CritereMailing('Branches', 'branches', Branches::Select($this->db), $this->attribut['mai_crit_bra'], $edition); 
CritereMailing('Zones', 'zones', Zones::Select($this->db), $this->attribut['mai_crit_zon'], $edition); 
CritereMailing('Utilisateurs', 'utilisateurs', Utilisateurs::Select($this->db), $this->attribut['mai_crit_uti'], $edition); 
?>
</div>
<?php
				if ($edition)
					dohtml_formbouton('labelagauche', 'Mettre à jour', 'maj_criteres');
?>
</form>
<?php
				break;
			case 2://liste
				echo "<form method='get' action='mailingliste.php?id={$this->Cle()}'>";
				$selection = (isset($_GET['selection']))? $_GET['selection']:-1;
				$adresse = (isset($_GET['adresse']))? $_GET['adresse']:-1;
				echo "<input type='hidden' name='id' value='{$this->Cle()}'/>";
				echo "Selection: <select name='selection'>
				<option value='-1'".(($selection==-1)?"selected='selected'":"").">Tous</option>
				<option value='0'".(($selection==0)?"selected='selected'":"").">Décoché</option>
				<option value='1'".(($selection==1)?"selected='selected'":"").">Coché</option>
				</select>";
				echo " Adresse: <select name='adresse'>
				<option value='-1'".(($adresse==-1)?"selected='selected'":"").">Tous</option>
				<option value='0'".(($adresse==0)?"selected='selected'":"").">e-mail manquante</option>
				<option value='1'".(($adresse==1)?"selected='selected'":"").">postale manquante</option>
				</select>";
				echo "<input type='submit' name='filtre' value='Filtrer'>";
				echo "</form>";
				Mailings::Tableau_mailing($this->db, $this->Cle(), $selection, $adresse);
				

				break;
			case 3://Texte-----------------------------------------------------
				$fichier=False;
				if ($this->attribut['mai_fichier']!=''){
					$fichiers = name2files($this->attribut['mai_fichier'], 'upload/');			
					if (count($fichiers)>0) {
						$fichier = True;
					}
					foreach	($fichiers as $key=>$value)
						$fichiers[$key] = str_replace('upload/','',$fichiers[$key]);
				}
				$checked = ($fichier)? "checked='checked'": '';
				$display = ($fichier)? '' : " style='display:none' ";
				echo "<h4>Contenu</h4>";
				if ($edition){
					echo "<input type='radio' $checked name='contenu' value='document' onclick=\"afficher('fichier')\">Document integré (pdf, jpg, etc)<br/>";
				}
				echo "<div id='fichier' $display>";
				if ($fichier){
					echo "Document actuel: <a href='upload/{$fichiers[0]}'>Consulter</a><br/>";
					echo "Aperçu: <br/>";
					for ($i = 1; $i < count($fichiers); $i++){
						echo "<a href='upload/{$fichiers[$i]}'><img alt='upload/{$fichiers[$i]}' src='thumbnail.php?src=upload/{$fichiers[$i]}&max=75'/></a>";
					}
				}
				else{
					echo "Aucun document";
				}
				if ($edition){
					echo "
<form method='POST' action='mailingtexte.php?id={$this->cle()}&edition={$edition}' enctype='multipart/form-data'>
    <input type='hidden' name='MAX_FILE_SIZE' value='<?=taille_maxi?>'>
    Fichier : <input type='file' name='avatar'>";
					if ($this->erreurs['mai_fichier']!='')
						echo $this->erreurs['mai_fichier'];
					echo "
    <input type='submit' name='send_content' value='Envoyer le fichier'>
</form>";
				}
				echo "</div>";
				$checked = ($fichier)? '': "checked='checked'";
				$display = ($fichier)? " style='display:none' ": '';
				if ($edition){		
					echo "<input type='radio' $checked name='contenu' value='texte' onclick=\"afficher('texte')\">Texte<br/>";	
				}					
				echo "<div id='texte' $display>";
				if (get_magic_quotes_gpc()){
				   $mai_texte = stripslashes( $this->attribut['mai_texte'] );
				}
				$mai_texte = $this->attribut['mai_texte'];
				if ($edition){
?>
<form method='post' action='mailingtexte.php?<?="id={$this->cle()}&edition=$edition"?>'>
	<textarea name='mai_texte' rows='20' style='width:95%'><?=$this->attribut['mai_texte']?></textarea>
<?php
	dohtml_formbouton('labelagauche', 'Mettre à jour', 'maj_infos', 'Annuler');
?>
</form>
<?php
				}
				else
					echo nl2br($mai_texte);
				echo "</div>";
				echo "<div>";
				echo "<h4>Pièces jointes</h4>";
				foreach($this->attribut['mai_attachment'] as $attachment)
					echo "<a href='upload/attachment/$attachment'>$attachment</a>";
				echo "
<form method='POST' action='mailingtexte.php?id={$this->cle()}&edition={$edition}' enctype='multipart/form-data'>
<input type='hidden' name='MAX_FILE_SIZE' value='<?=taille_maxi?>'>
Fichier : <input type='file' name='avatar'>";
				if ($this->erreurs['mai_fichier']!='')
					echo $this->erreurs['mai_fichier'];
				echo "
<input type='submit' name='send_attachment' value='Ajouter la pièce jointe'>
</form>";				
				echo "</div>";
				break;
				
			case 4://Envoi
				echo "<h4>Résumé</h4>";
				echo Branches::Mailing($this->db, $this->Cle());
				echo Zones::Mailing($this->db, $this->Cle());
				echo Utilisateurs::Mailing($this->db, $this->Cle());
				echo "<h4>Résultats</h4>";
				list ($mail, $postal) = Mailings::Resume_Mailing($this->db, $this->Cle());
				if ($postal)
					echo "<a href='maiexport.php?id={$this->Cle()}&mode=postal' alt='Télécharger le .pdf'>Envoi par courrier</a><br/><br/>";
				if ($mail)
					echo "<a href='maiexport.php?id={$this->Cle()}&mode=email' alt='Envoyer les mails'>Envoi par e-mail</a><br/>";
				echo "<a href='maiexport.php?id={$this->Cle()}&mode=emailtest' alt='Envoyer les mails'>Envoyer l'e-mail test</a><br/>";
				break;
			}
	}

}


?>
